﻿Imports System.Data.SqlClient
Imports System.Configuration.ConfigurationManager

Public Class DataAccess
    Private Shared m_instancia As DataAccess
    Private m_cadena As String = ""

    Public ReadOnly Property cadena() As String

        Get
            If m_cadena = "" Then
                m_cadena = ConnectionStrings("conexion").ConnectionString
            End If
            Return m_cadena
        End Get
    End Property

    Public Shared ReadOnly Property instancia() As DataAccess
        Get
            If m_instancia Is Nothing Then
                m_instancia = New DataAccess
            End If
            Return m_instancia
        End Get
    End Property

    Private Sub New()

    End Sub

    Public Function getConnection() As IDbConnection
        Return New SqlConnection(cadena)
    End Function

    Public Sub ejecutarSQL(ByVal p_sql As String, ByVal ParamArray p_parametros() As Parametro)
        Dim cmd As SqlCommand
        Dim objParameter As Parametro
        Dim nombre As String
        Dim estado As Boolean = True

        cmd = New SqlCommand
        cmd.Connection = getConnection()

        For Each objParameter In p_parametros
            nombre = "@" & objParameter.nombre
            cmd.Parameters.AddWithValue(nombre, objParameter.dato)
            p_sql = p_sql.Replace(objParameter.nombre, nombre)
        Next

        cmd.CommandText = p_sql
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
    End Sub

    Public Function ejecutarSQL(ByVal p_sql As String, ByVal p_conexion As IDbConnection, ByVal ParamArray p_parametros() As Parametro) As IDataReader
        Dim cmd As SqlCommand
        Dim parametro As Parametro
        Dim nombre As String

        cmd = New SqlCommand
        cmd.Connection = p_conexion

        If Not p_parametros Is Nothing Then
            For Each parametro In p_parametros
                nombre = String.Format("@{0}", parametro.nombre)
                cmd.Parameters.AddWithValue(nombre, parametro.dato)
                p_sql = p_sql.Replace(parametro.nombre, nombre)
            Next
        End If

        cmd.CommandText = p_sql

        If cmd.Connection.State = ConnectionState.Closed Then
            cmd.Connection.Open()
        End If

        Return cmd.ExecuteReader

    End Function

    Public Function ejecutarSQL(ByVal p_sql As String, ByVal p_tabla As String) As DataSet
        Dim adp As SqlDataAdapter
        Dim ds As New DataSet()
        adp = New SqlDataAdapter(p_sql, CType(getConnection(), SqlConnection))
        adp.Fill(ds, p_tabla)
        Return ds
    End Function
End Class